package Controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import EntityBean.Country;
import EntityBean.GovOffice;

public class ControllerDB {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	public void getConnection()
	{
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1130;databaseName=MockProject","sa" , "12345678");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public ArrayList<GovOffice> getListGov()
	{
		getConnection();
		ArrayList<GovOffice> listGov = new ArrayList<GovOffice>();
		GovOffice gov;
		try {
			ps= conn.prepareStatement("select * from GovernmentOfficeRegiontb  Join Countytb ON  GovernmentOfficeRegiontb.countyId = Countytb.countyId and active='yes'");
			rs = ps.executeQuery();
			while(rs.next())
			{
				gov = new GovOffice(rs.getString(1), rs.getString(2), rs.getString(3),  rs.getString(8), rs.getString(5));
				listGov.add(gov);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listGov;
		
	}
	
	public ArrayList<GovOffice> getAll()
	{
		getConnection();
		ArrayList<GovOffice> listGov = new ArrayList<GovOffice>();
		GovOffice gov;
		try {
			ps= conn.prepareStatement("select * from GovernmentOfficeRegiontb  Join Countytb ON  GovernmentOfficeRegiontb.countyId = Countytb.countyId");
			rs = ps.executeQuery();
			while(rs.next())
			{
				gov = new GovOffice(rs.getString(1), rs.getString(2), rs.getString(3),  rs.getString(8), rs.getString(5));
				listGov.add(gov);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listGov;
	}

	public ArrayList<GovOffice> getGv09(String first, String second)
	{
		getConnection();
		ArrayList<GovOffice> listGov = new ArrayList<GovOffice>();
		GovOffice gov;
		try {
			String query = "SELECT * FROM GovernmentOfficeRegiontb WHERE SUBSTRING(govOfficeName,1,1) BETWEEN '"+first+"' AND '"+second+"' AND  active='yes' ";
			ps= conn.prepareStatement(query);
			rs = ps.executeQuery();
			while(rs.next())
			{
				gov = new GovOffice(rs.getString(1), rs.getString(2), rs.getString(3),  rs.getString(4), rs.getString(5));
				listGov.add(gov);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return listGov;
	}
	
	public void updateDB(String name)
	{
		getConnection();
		
		try {
			
			String query = "UPDATE GovernmentOfficeRegiontb SET active='yes' WHERE govOfficeName='"+name+"' ";
			ps = conn.prepareStatement(query);
			ps.executeQuery();
			
			
			rs.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}
